
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/03/2011 23:12:31
-- Generated from EDMX file: C:\konst\projects\RouteTiming\RouteTiming.Data\RouteTimingModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ROUTE_TIMING];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_RouteRoutePoint]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RoutePointSet] DROP CONSTRAINT [FK_RouteRoutePoint];
GO
IF OBJECT_ID(N'[dbo].[FK_RoutePointEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EventSet] DROP CONSTRAINT [FK_RoutePointEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_ParticipantEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EventSet] DROP CONSTRAINT [FK_ParticipantEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_EventTypeEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EventSet] DROP CONSTRAINT [FK_EventTypeEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonRouteSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RouteScheduleSet] DROP CONSTRAINT [FK_PersonRouteSchedule];
GO
IF OBJECT_ID(N'[dbo].[FK_RouteRouteSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RouteScheduleSet] DROP CONSTRAINT [FK_RouteRouteSchedule];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[RoutePointSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RoutePointSet];
GO
IF OBJECT_ID(N'[dbo].[RouteSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RouteSet];
GO
IF OBJECT_ID(N'[dbo].[PersonSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonSet];
GO
IF OBJECT_ID(N'[dbo].[EventSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EventSet];
GO
IF OBJECT_ID(N'[dbo].[EventTypeSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EventTypeSet];
GO
IF OBJECT_ID(N'[dbo].[RouteScheduleSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RouteScheduleSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'RoutePointSet'
CREATE TABLE [dbo].[RoutePointSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(256)  NOT NULL,
    [OrderNo] int  NOT NULL,
    [Route_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'RouteSet'
CREATE TABLE [dbo].[RouteSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PersonSet'
CREATE TABLE [dbo].[PersonSet] (
    [Id] uniqueidentifier  NOT NULL,
    [FirstName] nvarchar(256)  NOT NULL,
    [MiddleName] nvarchar(256)  NOT NULL,
    [LastName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'EventSet'
CREATE TABLE [dbo].[EventSet] (
    [Id] uniqueidentifier  NOT NULL,
    [OrderNo] int IDENTITY(1,1) NOT NULL,
    [TimeRaised] datetime  NOT NULL,
    [TimeAdded] datetime  NOT NULL,
    [Desc] nvarchar(max)  NOT NULL,
    [RoutePoint_Id] uniqueidentifier  NULL,
    [Participant_Id] uniqueidentifier  NULL,
    [EventType_Id] int  NOT NULL
);
GO

-- Creating table 'EventTypeSet'
CREATE TABLE [dbo].[EventTypeSet] (
    [Id] int  NOT NULL,
    [Name] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'RouteScheduleSet'
CREATE TABLE [dbo].[RouteScheduleSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [StartTime] datetime  NOT NULL
);
GO

-- Creating table 'PersonRouteSchedule'
CREATE TABLE [dbo].[PersonRouteSchedule] (
    [Persons_Id] uniqueidentifier  NOT NULL,
    [PersonRouteSchedule_Person_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'RouteRouteSchedule'
CREATE TABLE [dbo].[RouteRouteSchedule] (
    [Routes_Id] uniqueidentifier  NOT NULL,
    [RouteSchedule_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'RoutePointSet'
ALTER TABLE [dbo].[RoutePointSet]
ADD CONSTRAINT [PK_RoutePointSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'RouteSet'
ALTER TABLE [dbo].[RouteSet]
ADD CONSTRAINT [PK_RouteSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PersonSet'
ALTER TABLE [dbo].[PersonSet]
ADD CONSTRAINT [PK_PersonSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [PK_EventSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'EventTypeSet'
ALTER TABLE [dbo].[EventTypeSet]
ADD CONSTRAINT [PK_EventTypeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'RouteScheduleSet'
ALTER TABLE [dbo].[RouteScheduleSet]
ADD CONSTRAINT [PK_RouteScheduleSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Persons_Id], [PersonRouteSchedule_Person_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [PK_PersonRouteSchedule]
    PRIMARY KEY NONCLUSTERED ([Persons_Id], [PersonRouteSchedule_Person_Id] ASC);
GO

-- Creating primary key on [Routes_Id], [RouteSchedule_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [PK_RouteRouteSchedule]
    PRIMARY KEY NONCLUSTERED ([Routes_Id], [RouteSchedule_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Route_Id] in table 'RoutePointSet'
ALTER TABLE [dbo].[RoutePointSet]
ADD CONSTRAINT [FK_RouteRoutePoint]
    FOREIGN KEY ([Route_Id])
    REFERENCES [dbo].[RouteSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RouteRoutePoint'
CREATE INDEX [IX_FK_RouteRoutePoint]
ON [dbo].[RoutePointSet]
    ([Route_Id]);
GO

-- Creating foreign key on [RoutePoint_Id] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [FK_RoutePointEvent]
    FOREIGN KEY ([RoutePoint_Id])
    REFERENCES [dbo].[RoutePointSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoutePointEvent'
CREATE INDEX [IX_FK_RoutePointEvent]
ON [dbo].[EventSet]
    ([RoutePoint_Id]);
GO

-- Creating foreign key on [Participant_Id] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [FK_ParticipantEvent]
    FOREIGN KEY ([Participant_Id])
    REFERENCES [dbo].[PersonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParticipantEvent'
CREATE INDEX [IX_FK_ParticipantEvent]
ON [dbo].[EventSet]
    ([Participant_Id]);
GO

-- Creating foreign key on [EventType_Id] in table 'EventSet'
ALTER TABLE [dbo].[EventSet]
ADD CONSTRAINT [FK_EventTypeEvent]
    FOREIGN KEY ([EventType_Id])
    REFERENCES [dbo].[EventTypeSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EventTypeEvent'
CREATE INDEX [IX_FK_EventTypeEvent]
ON [dbo].[EventSet]
    ([EventType_Id]);
GO

-- Creating foreign key on [Persons_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [FK_PersonRouteSchedule_Person]
    FOREIGN KEY ([Persons_Id])
    REFERENCES [dbo].[PersonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [PersonRouteSchedule_Person_Id] in table 'PersonRouteSchedule'
ALTER TABLE [dbo].[PersonRouteSchedule]
ADD CONSTRAINT [FK_PersonRouteSchedule_RouteSchedule]
    FOREIGN KEY ([PersonRouteSchedule_Person_Id])
    REFERENCES [dbo].[RouteScheduleSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonRouteSchedule_RouteSchedule'
CREATE INDEX [IX_FK_PersonRouteSchedule_RouteSchedule]
ON [dbo].[PersonRouteSchedule]
    ([PersonRouteSchedule_Person_Id]);
GO

-- Creating foreign key on [Routes_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [FK_RouteRouteSchedule_Route]
    FOREIGN KEY ([Routes_Id])
    REFERENCES [dbo].[RouteSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [RouteSchedule_Id] in table 'RouteRouteSchedule'
ALTER TABLE [dbo].[RouteRouteSchedule]
ADD CONSTRAINT [FK_RouteRouteSchedule_RouteSchedule]
    FOREIGN KEY ([RouteSchedule_Id])
    REFERENCES [dbo].[RouteScheduleSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RouteRouteSchedule_RouteSchedule'
CREATE INDEX [IX_FK_RouteRouteSchedule_RouteSchedule]
ON [dbo].[RouteRouteSchedule]
    ([RouteSchedule_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------